-- Copyright (c) 2016 Lucky Byte, Inc.
--
-- Summary : 平台对账
--
-- Author : xiaohu <xiaohu@lucky-byte.com>
-- Since  : 2016.2.3
--
-- History
--  2016.02.03 : 建立此文件
--  2016.03.01 : 完成第一版
--  2016.03.02 : 根据接口规范统计银联交易
--

-- 共享数据
local _check_vars = {}

-- 对账日期，清算日期
_check_vars.check_date  = '20160218'    -- os.date('%Y%m%d')
_check_vars.settl_date  = string.sub(_check_vars.check_date, 5, 8)

-- 邮件收件人/抄送人
local _recips_to = {
    ['yehai@ah.eptok.com'] = '叶海'
}
local _recips_cc = {
    ['xiaohu@lucky-byte.com'] = '吴小虎'
}

-- 发送对账邮件通知
local function send_error_mail(message)
    logger.error(message)

    local subject = '对账失败通知-' .. _check_vars.check_date
    local contents = string.format([[
        <html><head></head><body>
            <p>支付平台在对账时出现异常:</p>
            <p></p>
            <p style="color:red;">%s</p>
            <p></p>
            <p>对账日期：%s</p>
            <p></p>
            <p>请及时联系管理人员处理!</p>
            <hr>
            <p style="color:gray;">此邮件由 %s %s 自动发送，不需回复。</p>
            <p></p>
            <p style="color:gray; font-size:large;">安徽银盛支付</p>
        </body></html>
    ]], message, _check_vars.check_date, runtime.product, runtime.version)
    if not mail.send(subject, contents, _recips_to, _recips_cc) then
        logger.error('发送对账通知邮件失败.')
    end
end


-- 对账汇总文件模板
_check_vars.pay_sum_tmpl = runtime.basedir .. '/check/tmpl/pay_sum.html'
local _f = io.open(_check_vars.pay_sum_tmpl)
if not _f then
    send_error_mail(string.format('对账汇总模板文件[%s]不存在，对账失败.',
        _check_vars.pay_sum_tmpl))
    return false
end
_f:close()

-- 对账结果文件存放目录, 如果不存在则创建之
_check_vars.check_path = string.format('%s/check/%s/%s',
    runtime.basedir, string.sub(_check_vars.check_date, 1, 6),
    _check_vars.check_date)
local _f = io.open(_check_vars.check_path, "r")
if not _f then
    os.execute('mkdir -p ' .. _check_vars.check_path)
else
    _f:close()
end

-- 数据导入模块
local _dbload = require('check/dbload')

-- 银联流水文件，每天银联自动下发
local _upay_path = '/tmp'
local _upay_date = string.sub(_check_vars.check_date, 3, 8)
local _coma_name = string.format('IND%s32ACOMA', _upay_date)
local _coma_path = string.format('%s/%s', _upay_path, _coma_name)
local _f = io.open(_coma_path)
if not _f then
    send_error_mail(string.format(
        '银联对账流水文件[%s]不存在，对账失败.', _coma_path))
    return false
end
_f:close()

-- 拷贝银联流水数据文件到对账目录
logger.debug(string.format(
    '拷贝银联对账流水文件[%s]到[%s].', _coma_path, _check_vars.check_path))
if os.execute(string.format('cp -u %s %s/%s',
    _coma_path, _check_vars.check_path, _coma_name)) == 1 then
    send_error_mail(string.format(
        '拷贝银联对账流水文件[%s]到[%s]失败，对账失败.',
        _coma_path, _check_vars.check_path))
end

-- 装载 COMA 数据
if not _dbload.load_coma(_coma_path) then
    send_error_mail(string.format(
        '导入银联对账流水文件[%s]失败，COMA对账失败.', coma_path))
    return false
end

-- 平台接出到银联交易汇总
local _sql = [[
    select
        count(trans.*) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    where
        trans.settl_needs = true and  -- 清算交易
        trans.direct = 2 and          -- 平台发送到银联
        trans.settl_date = ? and      -- 清算日期
        trans.status = 0 and          -- 状态正常
        trans.resp_code = '00' and    -- 交易成功
        trans.norm in                 -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3')
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
local _record = _table.get_record(0)
_check_vars.bytepay = {
    num = tonumber(_record.get('num')),
    amt = tonumber(_record.get('amt')) or 0.00,
}

-- 平台多出交易汇总
local _sql = [[
    select
        count(trans.*) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    where
        trans.settl_needs = true and      -- 清算交易
        trans.direct = 2 and              -- 平台发送到银联
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3') and
        trans.retref_num not in           -- 检索参考号不在银联流水中
            (select substring(coma.trans_time from 5 for 6) || coma.trace_num
                from upay_coma as coma where
                    coma.settl_date = ? and     -- 清算日期
                    coma.reverse != 'R' and     -- 忽略已冲正交易
                    coma.revoke != 'C'  and     -- 忽略已撤销交易
                    coma.acpt_id in             -- 仅统计平台内存在的商户
                        (select mer.merid from pay_merch as mer
                            where mer.avail = true))
]]
local _table = jdbc.select(_sql,
    _check_vars.settl_date, _check_vars.settl_date)
local _record = _table.get_record(0)
table.merge(_check_vars.bytepay, {
    specs_num = tonumber(_record.get('num')),
    specs_amt = tonumber(_record.get('amt')) or 0.00,
})

-- 统计平台有银联无的交易
local _sql = [[
    select
        trans.trans_name,
        trans.acpt_id,
        mer.name as mer_name,
        trans.term_id,
        trans.local_date || trans.local_time as trans_time,
        CAST(coalesce(trans.trans_amt, '0') as decimal(18,2)) / 100 as trans_amt,
        trans.trace_num,
        trans.retref_num
    from
        pay_trans as trans left join pay_merch as mer on mer.merid = acpt_id
    where
        trans.settl_needs = true and      -- 清算交易
        trans.direct = 2 and              -- 平台发送到银联
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3') and
        trans.retref_num not in           -- 检索参考号不在银联流水中
            (select substring(coma.trans_time from 5 for 6) || coma.trace_num
                from upay_coma as coma where
                    coma.settl_date = ? and     -- 清算日期
                    coma.reverse != 'R' and     -- 忽略已冲正交易
                    coma.revoke != 'C'  and     -- 忽略已撤销交易
                    coma.acpt_id in             -- 仅统计平台内存在的商户
                        (select mer.merid from pay_merch as mer
                            where mer.avail = true))
    order by trans.serial
]]
local _table = jdbc.select(_sql,
    _check_vars.settl_date, _check_vars.settl_date)
_check_vars.bytepay.specs = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.bytepay.specs[i] = {
            trans_name = _record.get('trans_name'),
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            term_id    = _record.get('term_id'),
            trans_time = _record.get('trans_time'),
            trans_amt  = _record.get('trans_amt'),
            trace_num  = _record.get('trace_num'),
            retref_num = _record.get('retref_num'),
        }
    end
end

-- 银联 COMA 交易汇总
local _sql = [[
    select
        count(coma.*) as num,
        sum(CAST(coalesce(coma.trans_amt, '0') as decimal(18,2))) / 100 as amt
    from
        upay_coma as coma
    where
        coma.settl_date = ? and     -- 清算日期
        coma.reverse != 'R' and     -- 忽略已冲正交易
        coma.revoke != 'C'  and     -- 忽略已撤销交易
        coma.acpt_id in             -- 仅统计平台内存在的商户
            (select mer.merid from pay_merch as mer where mer.avail = true)
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
local _record = _table.get_record(0)
_check_vars.coma = {
    num = tonumber(_record.get('num')),
    amt = tonumber(_record.get('amt')) or 0.00,
}

-- 银联多出交易汇总
local _sql = [[
    select
        count(coma.*) as num,
        sum(CAST(coalesce(coma.trans_amt, '0') as decimal(18,2))) / 100 as amt
    from
        upay_coma as coma
    where
        coma.settl_date = ? and      -- 清算日期
        coma.reverse != 'R' and      -- 忽略已冲正交易
        coma.revoke != 'C'  and      -- 忽略已撤销交易
        coma.acpt_id in              -- 仅统计平台内存在的商户
            (select mer.merid from pay_merch as mer where mer.avail = true) and
        substring(coma.trans_time from 5 for 6) || coma.trace_num not in
            (select trans.retref_num from pay_trans as trans where
                trans.settl_needs = true and      -- 清算交易
                trans.direct = 2 and              -- 平台发送到银联
                trans.settl_date = ? and          -- 清算日期
                trans.status = 0 and              -- 状态正常
                trans.resp_code = '00' and        -- 交易成功
                trans.norm in                     -- 银联交易
                    ('009.1', '009.6', '035.1', '035.2', '035.3')
]]
local _table = jdbc.select(_sql,
    _check_vars.settl_date, _check_vars.settl_date)
local _record = _table.get_record(0)
table.merge(_check_vars.coma, {
    specs_num = tonumber(_record.get('num')),
    specs_amt = tonumber(_record.get('amt')) or 0.00,
})

-- 统计银联有平台无的交易
local _sql = [[
    select
        coma.msg_type || '-' || coma.proc_code as trans_name,
        coma.acpt_id,
        coma.mer_name,
        coma.term_id,
        coma.trans_time,
        CAST(coalesce(coma.trans_amt, '0') as decimal(18,2)) / 100 as trans_amt,
        coma.trace_num,
        substring(coma.trans_time from 5 for 6) || coma.trace_num as retref_num
    from
        upay_coma as coma
    where
        coma.settl_date = ? and      -- 清算日期
        coma.reverse != 'R' and      -- 忽略已冲正交易
        coma.revoke != 'C'  and      -- 忽略已撤销交易
        coma.acpt_id in              -- 仅统计平台内存在的商户
            (select mer.merid from pay_merch as mer where mer.avail = true) and
        substring(coma.trans_time from 5 for 6) || coma.trace_num not in
            (select trans.retref_num from pay_trans as trans where
                trans.settl_needs = true and      -- 清算交易
                trans.direct = 2 and              -- 平台发送到银联
                trans.settl_date = ? and          -- 清算日期
                trans.status = 0 and              -- 状态正常
                trans.resp_code = '00' and        -- 交易成功
                trans.norm in                     -- 银联交易
                    ('009.1', '009.6', '035.1', '035.2', '035.3')
    order by coma.trans_time
]]
local _table = jdbc.select(_sql,
    _check_vars.settl_date, _check_vars.settl_date)
_check_vars.coma.specs = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.coma.specs[i] = {
            trans_name = _record.get('trans_name'),
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            term_id    = _record.get('term_id'),
            trans_time = _record.get('trans_time'),
            trans_amt  = _record.get('trans_amt'),
            trace_num  = _record.get('trace_num'),
            retref_num = _record.get('retref_num'),
        }
    end
end

-- 平台内交易按商户汇总(平台接出)
local _sql = [[
    select
        trans.acpt_id,
        mer.name as mer_name,
        count(trans.acpt_id) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    left join pay_merch as mer on
        mer.merid = trans.acpt_id
    where
        trans.direct = 2 and              -- 平台发送到银联
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3')
    group by
        trans.acpt_id, mer.name
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
_check_vars.merch_sum_out = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.merch_sum_out[i] = {
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            num        = _record.get('num'),
            amt        = _record.get('amt'),
        }
    end
end

-- 平台内交易按商户汇总(平台接入)
local _sql = [[
    select
        trans.acpt_id,
        mer.name as mer_name,
        count(trans.acpt_id) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    left join pay_merch as mer on
        mer.merid = trans.acpt_id
    where
        trans.direct = 1 and              -- 终端发送到平台
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3')
    group by
        trans.acpt_id, mer.name
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
_check_vars.merch_sum_in = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.merch_sum_in[i] = {
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            num        = _record.get('num'),
            amt        = _record.get('amt'),
        }
    end
end

-- 平台交易按终端汇总(接出)
local _sql = [[
    select
        trans.term_id,
        trans.acpt_id,
        mer.name as mer_name,
        count(trans.term_id) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    left join pay_merch as mer on
        mer.merid = trans.acpt_id
    where
        trans.direct = 2 and              -- 平台发送到银联
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3')
    group by
        trans.term_id, trans.acpt_id, mer.name
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
_check_vars.term_sum_out = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.term_sum_out[i] = {
            term_id    = _record.get('term_id'),
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            num        = _record.get('num'),
            amt        = _record.get('amt'),
        }
    end
end

-- 平台交易按终端汇总(接入)
local _sql = [[
    select
        trans.term_id,
        trans.acpt_id,
        mer.name as mer_name,
        count(trans.term_id) as num,
        sum(CAST(coalesce(trans.trans_amt , '0') as numeric(18,2))) / 100 as amt
    from
        pay_trans as trans
    left join pay_merch as mer on
        mer.merid = trans.acpt_id
    where
        trans.direct = 1 and              -- 平台发送到银联
        trans.settl_date = ? and          -- 清算日期
        trans.status = 0 and              -- 状态正常
        trans.resp_code = '00' and        -- 交易成功
        trans.norm in                     -- 银联交易
            ('009.1', '009.6', '035.1', '035.2', '035.3')
    group by
        trans.term_id, trans.acpt_id, mer.name
]]
local _table = jdbc.select(_sql, _check_vars.settl_date)
_check_vars.term_sum_in = {}
if _table.count > 0 then
    for i = 1, _table.count do
        local _record = _table.get_record(i - 1)
        _check_vars.term_sum_in[i] = {
            term_id    = _record.get('term_id'),
            acpt_id    = _record.get('acpt_id'),
            mer_name   = _record.get('mer_name'),
            num        = _record.get('num'),
            amt        = _record.get('amt'),
        }
    end
end

-- 对账汇总文件名
_check_vars.pay_sum_html = string.format('%s/PAY_%s_SUM.html',
    _check_vars.check_path, _check_vars.check_date)
_check_vars.pay_sum_pdf = string.format('%s/PAY_%s_SUM.pdf',
    _check_vars.check_path, _check_vars.check_date)

-- 生成 HTML 对账汇总
if not report.proc_ftl(_check_vars.pay_sum_tmpl, 'utf-8',
    _check_vars.pay_sum_html, 'utf-8', _check_vars) then
    send_error_mail(string.format('处理对账汇总模板文件[%s]失败.',
        _check_vars.pay_sum_tmpl))
    return false
end
local _attach_files = {
    _check_vars.pay_sum_html
}

-- 生成 PDF 对账汇总
local _pdf_params = {
    header_left = '[doctitle]',
    header_right = string.format('对账日期 %s 年 %s 月 %s 日',
        string.sub(_check_vars.check_date, 1, 4),
        string.sub(_check_vars.check_date, 5, 6),
        string.sub(_check_vars.check_date, 7, 8)),
    header_line = 'true',
    footer_left = '安徽银盛支付',
    footer_center = '第 [page] 页 共 [topage] 页',
    footer_right = '制表日期 ' .. os.date('%Y 年 %m 月 %d 日'),
    footer_line = 'true',
}
if not report.proc_pdf(_check_vars.pay_sum_html, '银盛支付对账汇总表',
    'utf-8', _check_vars.pay_sum_pdf, _pdf_params) then
    logger.warn('创建 PDF 报表失败，仅发送 HTML 格式报表，请根据日志分析失败原因.')
else
    table.insert(_attach_files, _check_vars.pay_sum_pdf)
end

-- 将对账结果文件发送到指定的邮箱
local _subject = '对账汇总表-' .. _check_vars.check_date
local _contents = string.format([[
    <html><head></head><body>
        <p>您好，附件为 %s 年 %s 月 %s 日对账汇总表，请查收!</p>
        <hr>
        <p style="color:gray;">此邮件由 %s %s 自动发送，不需回复。</p>
        <p></p>
        <p style="color:gray; font-size:large;">安徽银盛支付</p>
    </body></html>
]], string.sub(_check_vars.check_date, 1, 4),
    string.sub(_check_vars.check_date, 5, 6),
    string.sub(_check_vars.check_date, 7, 8),
    runtime.product, runtime.version)
if not mail.send(_subject, _contents,
    _recips_to, _recips_cc, table.unpack(_attach_files)) then
    send_error_mail('通过邮件发送支付系统对账汇总表失败，' ..
        '请检查邮件服务器和网络配置是否正确。' ..
        '对账汇总文件已生成并存放在服务器的指定目录中，' ..
        '系统管理人员可以通过 FTP 或其它维护工具下载。')
end

return true
